package com.njcky.service;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.njcky.model.dto.QueryPipelineLengthDTO;
import com.njcky.model.vo.ComboVO;
import com.njcky.model.vo.PairVO;
import com.njcky.model.vo.QueryResultVO;
import com.njcky.utils.db.DBPool2DataSourceUtil;
import com.njcky.utils.page.StringUtils;
import com.njcky.utils.page.entity.TableDataInfo;
import lombok.extern.slf4j.Slf4j;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;

import java.util.concurrent.*;

import java.math.BigDecimal;
import java.util.*;

/**
 * 连通性分析和距离实现服务
 */
@Slf4j
@Service
public class ConnectivityDistanceAnalysisNew {

    private static final Logger logger = LoggerFactory.getLogger(ConnectivityDistanceAnalysisNew.class);
    private static final ObjectMapper objectMapper = new ObjectMapper();
    // 线程池查询超时时间（避免无限阻塞，可根据业务调整）
    private static final long QUERY_TIMEOUT_SEC = 5;

    @Autowired
    DBPool2DataSourceUtil pool2DataSourceUtil;

    /**
     * 连通性分析数据
     *
     * @param dto
     * @return
     */
    public TableDataInfo connectivityPublic(QueryPipelineLengthDTO dto) {
        // 1. 基础参数获取与校验（不变）
        String firstPoslon = dto.getFirstPoslon();
        String firstPoslat = dto.getFirstPoslat();
        String endPoslon = dto.getEndPoslon();
        String endPoslat = dto.getEndPoslat();
        String tableName = dto.getTables().get(0);
        int pageSize = dto.getPageSize();
        int pageNum = dto.getPageNum();

        // 2. 查询两个点的完整信息（含source和target）（不变）
        Map<String, Object> objectOneMap = connectivityOnePoint(firstPoslon, firstPoslat, tableName);
        if (ObjectUtils.isEmpty(objectOneMap) || !objectOneMap.containsKey("source") || !objectOneMap.containsKey("target")) {
            logger.warn("【连通性分析】第一个点查询结果为空，或缺少source/target字段");
            return null;
        }
        Map<String, Object> objectTwoMap = connectivityOnePoint(endPoslon, endPoslat, tableName);
        if (ObjectUtils.isEmpty(objectTwoMap) || !objectTwoMap.containsKey("source") || !objectTwoMap.containsKey("target")) {
            logger.warn("【连通性分析】第二个点查询结果为空，或缺少source/target字段");
            return null;
        }

        // 3. 提取4个核心值（不变）
        String oneSource = getSafeString(objectOneMap.get("source"));
        String oneTarget = getSafeString(objectOneMap.get("target"));
        String twoSource = getSafeString(objectTwoMap.get("source"));
        String twoTarget = getSafeString(objectTwoMap.get("target"));

        if (StringUtils.isEmpty(oneSource) || StringUtils.isEmpty(oneTarget)
                || StringUtils.isEmpty(twoSource) || StringUtils.isEmpty(twoTarget)) {
            logger.error("【连通性分析】点信息的source/target为空，oneSource:{}, oneTarget:{}, twoSource:{}, twoTarget:{}",
                    oneSource, oneTarget, twoSource, twoTarget);
            return null;
        }

        // 4. 生成4种组合（不变）
        List<PairVO> combinations = Arrays.asList(
                new PairVO(oneSource, twoSource, "oneSource->twoSource"),
                new PairVO(oneSource, twoTarget, "oneSource->twoTarget"),
                new PairVO(oneTarget, twoSource, "oneTarget->twoSource"),
                new PairVO(oneTarget, twoTarget, "oneTarget->twoTarget")
        );

        // 5. 线程池初始化（不变）
        ExecutorService executor = Executors.newFixedThreadPool(4, r -> {
            Thread thread = new Thread(r);
            thread.setName("connectivity-query-thread-" + thread.getId());
            return thread;
        });
        List<Future<List<Map<String, Object>>>> futures = new ArrayList<>();

        try {
            // 6. 提交所有组合的查询任务（不变）
            for (PairVO pair : combinations) {
                final PairVO currentPair = pair;
                futures.add(executor.submit(() -> {
                    List<Map<String, Object>> result = connectivityTwoPoint(
                            currentPair.getFirst(), currentPair.getSecond(), tableName, pageSize, pageNum);
                    return result;
                }));
            }

            // 7. 等待所有任务完成，筛选【total最大】的组合（核心修改）
            List<Map<String, Object>> bestMapList = null;
            long maxTotal = -1;  // 记录最大的total值（用long匹配total的类型）
            String bestCombination = "";

            for (int i = 0; i < futures.size(); i++) {
                Future<List<Map<String, Object>>> future = futures.get(i);
                PairVO currentPair = combinations.get(i);

                try {
                    List<Map<String, Object>> currentList = future.get(QUERY_TIMEOUT_SEC, TimeUnit.SECONDS);
                    // 计算当前组合的total值（核心修改：从结果中提取total）
                    long currentTotal = 0;
                    if (currentList != null && !currentList.isEmpty() && currentList.get(0).containsKey("total")) {
                        // 安全转换total（避免格式错误）
                        try {
                            currentTotal = Long.parseLong(getSafeString(currentList.get(0).get("total")));
                        } catch (NumberFormatException e) {
                            logger.error("【连通性分析】组合[{}]的total格式错误，值为:{}",
                                    currentPair.getName(), currentList.get(0).get("total"), e);
                            currentTotal = 0;
                        }
                    }
                    // 更新最佳结果：仅当当前total大于历史最大total时（核心修改）
                    if (currentTotal > maxTotal) {
                        maxTotal = currentTotal;
                        bestMapList = currentList;
                    }

                } catch (TimeoutException e) {
                    logger.error("【连通性分析】组合[{}]查询超时（{}秒），已跳过", currentPair.getName(), QUERY_TIMEOUT_SEC, e);
                    future.cancel(true);
                } catch (InterruptedException | ExecutionException e) {
                    logger.error("【连通性分析】组合[{}]查询异常，已跳过", currentPair.getName(), e);
                }
            }

            // 8. 最佳结果校验（不变）
            if (bestMapList == null || bestMapList.isEmpty()) {
                logger.warn("【连通性分析】所有组合查询结果均为空");
                return null;
            }
            // 9. shapetext格式转换（不变）
            for (Map<String, Object> map : bestMapList) {
                if (map.containsKey("shapetext")) {
                    String originalShape = getSafeString(map.get("shapetext"));
                    String convertedShape = convertShapeText(originalShape);
                    map.put("shapetext", convertedShape);
                }
            }

            // 10. 构建返回结果（不变，total仍取最佳组合的实际total）
            TableDataInfo dataInfo = new TableDataInfo();
            dataInfo.setCode(200);
            dataInfo.setMsg("查询成功！");
            dataInfo.setTotal(bestMapList.get(0).containsKey("total") ?
                    Long.parseLong(getSafeString(bestMapList.get(0).get("total"))) :
                    (long) bestMapList.size());
            dataInfo.setData(bestMapList);
            return dataInfo;

        } finally {
            // 关闭线程池（不变）
            executor.shutdown();
            try {
                if (!executor.awaitTermination(10, TimeUnit.SECONDS)) {
                    executor.shutdownNow();
                }
            } catch (InterruptedException e) {
                executor.shutdownNow();
            }
        }
    }

//    public TableDataInfo connectivityPublic(QueryPipelineLengthDTO dto) {
//        // 1. 基础参数获取与校验
//        String firstPoslon = dto.getFirstPoslon();
//        String firstPoslat = dto.getFirstPoslat();
//        String endPoslon = dto.getEndPoslon();
//        String endPoslat = dto.getEndPoslat();
//        String tableName = dto.getTables().get(0);
//        int pageSize = dto.getPageSize();
//        int pageNum = dto.getPageNum();
//
//        // 2. 查询两个点的完整信息（含source和target）
//        Map<String, Object> objectOneMap = connectivityOnePoint(firstPoslon, firstPoslat, tableName);
//        if (ObjectUtils.isEmpty(objectOneMap) || !objectOneMap.containsKey("source") || !objectOneMap.containsKey("target")) {
//            logger.warn("【连通性分析】第一个点查询结果为空，或缺少source/target字段");
//            return null;
//        }
//        Map<String, Object> objectTwoMap = connectivityOnePoint(endPoslon, endPoslat, tableName);
//        if (ObjectUtils.isEmpty(objectTwoMap) || !objectTwoMap.containsKey("source") || !objectTwoMap.containsKey("target")) {
//            logger.warn("【连通性分析】第二个点查询结果为空，或缺少source/target字段");
//            return null;
//        }
//
//        // 3. 关键修改1：提取两个点的「4个核心值」（source+target），并安全转换（避免空指针）
//        String oneSource = getSafeString(objectOneMap.get("source")); // 第一个点的source
//        String oneTarget = getSafeString(objectOneMap.get("target")); // 第一个点的target
//        String twoSource = getSafeString(objectTwoMap.get("source")); // 第二个点的source
//        String twoTarget = getSafeString(objectTwoMap.get("target")); // 第二个点的target
//
//        // 校验4个核心值非空（避免无效组合）
//        if (StringUtils.isEmpty(oneSource) || StringUtils.isEmpty(oneTarget)
//                || StringUtils.isEmpty(twoSource) || StringUtils.isEmpty(twoTarget)) {
//            logger.error("【连通性分析】点信息的source/target为空，oneSource:{}, oneTarget:{}, twoSource:{}, twoTarget:{}",
//                    oneSource, oneTarget, twoSource, twoTarget);
//            return null;
//        }
//        // 4. 关键修改2：基于4个核心值生成「排列组合」（共4种核心组合，覆盖两点间所有可能连通方向）
//        List<PairVO> combinations = Arrays.asList(
//                new PairVO(oneSource, twoSource, "oneSource->twoSource"),  // 第一个点source → 第二个点source
//                new PairVO(oneSource, twoTarget, "oneSource->twoTarget"),  // 第一个点source → 第二个点target
//                new PairVO(oneTarget, twoSource, "oneTarget->twoSource"),  // 第一个点target → 第二个点source
//                new PairVO(oneTarget, twoTarget, "oneTarget->twoTarget")   // 第一个点target → 第二个点target
//        );
//
//        // 5. 线程池初始化（固定4线程，匹配4种组合；建议用线程池工厂，避免硬编码）
//        ExecutorService executor = Executors.newFixedThreadPool(4, r -> {
//            Thread thread = new Thread(r);
//            thread.setName("connectivity-query-thread-" + thread.getId()); // 线程命名，便于排查
//            return thread;
//        });
//        List<Future<List<Map<String, Object>>>> futures = new ArrayList<>();
//
//        try {
//            // 6. 提交所有组合的查询任务（并行执行）
//            for (PairVO pair : combinations) {
//                final PairVO currentPair = pair; // lambda需final变量
//                futures.add(executor.submit(() -> {
//                    // 执行当前组合的连通性查询
//                    List<Map<String, Object>> result = connectivityTwoPoint(
//                            currentPair.getFirst(), currentPair.getSecond(), tableName, pageSize, pageNum);
//                    return result;
//                }));
//            }
//
//            // 7. 等待所有任务完成，筛选结果数量最多的组合
//            List<Map<String, Object>> bestMapList = null;
//            int maxSize = -1;
//            String bestCombination = "";
//            for (int i = 0; i < futures.size(); i++) {
//                Future<List<Map<String, Object>>> future = futures.get(i);
//                PairVO currentPair = combinations.get(i);
//                try {
//                    // 关键优化：设置超时时间，避免单个任务阻塞整体流程
//                    List<Map<String, Object>> currentList = future.get(QUERY_TIMEOUT_SEC, TimeUnit.SECONDS);
//                    int currentSize = (currentList != null) ? currentList.size() : 0;
//                    // 更新最佳结果（仅当当前数量大于历史最大值时）
//                    if (currentSize > maxSize) {
//                        maxSize = currentSize;
//                        bestMapList = currentList;
//                        bestCombination = currentPair.getName();
//                    }
//                } catch (TimeoutException e) {
//                    logger.error("【连通性分析】组合[{}]查询超时（{}秒），已跳过", currentPair.getName(), QUERY_TIMEOUT_SEC, e);
//                    future.cancel(true); // 超时任务取消，释放线程
//                } catch (InterruptedException | ExecutionException e) {
//                    logger.error("【连通性分析】组合[{}]查询异常，已跳过", currentPair.getName(), e);
//                }
//            }
//
//            // 8. 最佳结果校验
//            if (bestMapList == null || bestMapList.isEmpty()) {
//                logger.warn("【连通性分析】所有组合查询结果均为空");
//                return null;
//            }
//            logger.info("【连通性分析】所有组合查询完成，最终最佳组合是[{}]，结果数量: {}", bestCombination, maxSize);
//
//            // 9. 原有逻辑：shapetext格式转换
//            for (Map<String, Object> map : bestMapList) {
//                if (map.containsKey("shapetext")) {
//                    String originalShape = getSafeString(map.get("shapetext"));
//                    String convertedShape = convertShapeText(originalShape);
//                    map.put("shapetext", convertedShape);
//                }
//            }
//            // 10. 构建返回结果（total建议校验，避免空指针）
//            TableDataInfo dataInfo = new TableDataInfo();
//            dataInfo.setCode(200);
//            dataInfo.setMsg("查询成功！");
//            // 安全处理total：若第一个元素无total，默认用结果数量
//            dataInfo.setTotal(bestMapList.get(0).containsKey("total") ?
//                    Long.parseLong(getSafeString(bestMapList.get(0).get("total"))) :
//                    (long) bestMapList.size());
//            dataInfo.setData(bestMapList);
//            return dataInfo;
//        } finally {
//            // 关键：无论结果如何，关闭线程池（避免资源泄漏）
//            executor.shutdown();
//            // 等待线程池关闭（可选，根据业务是否需要阻塞）
//            try {
//                if (!executor.awaitTermination(10, TimeUnit.SECONDS)) {
//                    executor.shutdownNow(); // 强制关闭未完成任务
//                }
//            } catch (InterruptedException e) {
//                executor.shutdownNow();
//            }
//        }
//    }

    /**
     * 安全转换Object为String（避免null.toString()异常）
     */
    private String getSafeString(Object obj) {
        return obj == null ? "" : obj.toString().trim();
    }


    /**
     * 将MULTILINESTRING转换为LINESTRING并处理坐标
     */
    private String convertShapeText(String original) {
        String lineString = original.replace("MULTILINESTRING", "LINESTRING");
        lineString = lineString.replace("((", "(").replace("))", ")");
        return lineString;
    }

    // 根据输入的起点获取相关的数据集
    public Map<String, Object> connectivityOnePoint(String lon, String lat, String tableName) {
        String sql = "SELECT " +
                "gid, target, source," +
                "ST_Distance(" +
                "   ST_Transform(ST_SetSRID(ST_MakePoint(" + lon + ", " + lat + "), 4326), 4549)," +
                "   geom " +
                ") AS distancea," +
                "geom " +
                "FROM " + tableName + " " +
                "ORDER BY " +
                "distancea " +
                "LIMIT 1;";
        Map<String, Object> objectMap = pool2DataSourceUtil.NpgNormal_GetListDicNo1(sql);
        return objectMap;
    }

//
//    public List<Map<String, Object>> connectivityTwoPoint(
//            String source,
//            String target,
//            String tableName,
//            Integer pageSize,
//            Integer pageNum
//    ) {
//        // 构建WITH子句SQL，通过嵌套子查询阻断谓词下推，确保total基于全量数据
//        StringBuilder sqlBuilder = new StringBuilder();
//        // 1. 定义全量路径数据（嵌套子查询封装，避免被分页影响）
//        sqlBuilder.append("WITH full_route_data AS (")
//                .append("    SELECT * FROM (")  // 关键：外层子查询阻断谓词下推
//                .append("        SELECT ")
//                .append("            a.*, ")
//                .append("            a.xzq AS ssqy, ")  // 行政区域别名
//                .append("            a.zydw AS tcdw, ")  // 责任单位别名
//                .append("            ST_AsText(ST_Transform(a.geom, 4490)) AS shapetext ")  // 几何文本转换
//                .append("        FROM ")
//                .append("            ").append(tableName).append(" a, ")  // 动态表名
//                .append("            (SELECT * FROM pgr_dijkstra ( ")  // pgr_dijkstra路径查询
//                .append("                'SELECT gid AS id, source::integer, target::integer, length::double precision AS cost FROM ").append(tableName).append("', ")
//                .append("                ").append(source).append(", ").append(target).append(", FALSE, FALSE ")  // 动态source/target
//                .append("            )) T ")
//                .append("        WHERE ")
//                .append("            a.gid = T.id2 ")  // 关联路径结果与原表
//                .append("    ) AS temp ")  // 必须添加临时别名，否则语法错误
//                .append("), ")
//                // 2. 基于全量数据计算total（独立子句，与分页无关）
//                .append("total_count AS (")
//                .append("    SELECT COUNT(*) AS total FROM full_route_data ")
//                .append(") ")
//                // 3. 主查询：从全量数据分页，关联total
//                .append("SELECT ")
//                .append("    frd.*, ")
//                .append("    tc.total ")  // total来自全量统计
//                .append("FROM ")
//                .append("    full_route_data frd, ")
//                .append("    total_count tc ");  // 笛卡尔积关联，每条数据都带total
//        // 4. 动态添加分页条件（仅影响返回的记录，不影响total）
//        if (pageSize != null && pageNum != null && pageSize > 0 && pageNum > 0) {
//            int offset = (pageNum - 1) * pageSize;  // offset从0开始，pageNum=1时offset=0
//            sqlBuilder.append("LIMIT ").append(pageSize)
//                    .append(" OFFSET ").append(offset);
//        }
//        // 转换为最终SQL并执行
//        String sql = sqlBuilder.toString();
//        List<Map<String, Object>> connectivity = pool2DataSourceUtil.NpgNormal_GetListDic_getBsmDateParallel(sql);
//        return connectivity;
//    }


    public List<Map<String, Object>> connectivityTwoPoint(
            String source,
            String target,
            String tableName,
            Integer pageSize,
            Integer pageNum
    ) {
        // 构建基础SQL
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("SELECT ")
                .append("a.*, ")
                .append("a.xzq as ssqy, ")
                .append("a.zydw as tcdw, ")
                .append("st_astext(st_transform(a.geom, 4490)) as shapetext, ")
                .append("(SELECT COUNT(*) FROM ").append(tableName).append(" a2, ")  // 总记录数子查询
                .append("   (SELECT * FROM pgr_dijkstra ( ")
                .append("       'SELECT gid AS id, source::integer, target::integer, length::double precision AS cost FROM ").append(tableName).append("', ")
                .append(source).append(", ").append(target).append(", FALSE, FALSE")
                .append("   )) T2 ")
                .append("WHERE a2.gid = T2.id2) as total  ")
                .append("FROM ")
                .append(tableName).append(" a, ")
                .append("(SELECT * FROM pgr_dijkstra ( ")
                .append("   'SELECT gid AS id, source::integer, target::integer, length::double precision AS cost FROM ").append(tableName).append("', ")
                .append(source).append(", ").append(target).append(", FALSE, FALSE")
                .append(") ) T ")
                .append("WHERE ")
                .append("a.gid = T.id2 ");
        // 动态添加分页条件（仅当pageSize和pageNum都不为null时）
        if (pageSize != null && pageNum != null && pageSize > 0 && pageNum > 0) {
            int offset = (pageNum - 1) * pageSize;
            sqlBuilder.append("LIMIT ").append(pageSize).append(" OFFSET ").append(offset);
        }
        // 转换为最终SQL字符串
        String sql = sqlBuilder.toString();
        // 执行查询（需先校验参数安全性）
        List<Map<String, Object>> connectivity = pool2DataSourceUtil.NpgNormal_GetListDic_getBsmDateParallel(sql);
        return connectivity;
    }


    /**
     * 连通性分析返回参数转换 （connectivity）
     * TODO 工具
     *
     * @param rawDataList
     * @return
     */
    public List<Map<String, Object>> processDataFromTableData(List<?> rawDataList) {
        // 空列表处理
        if (rawDataList == null || rawDataList.isEmpty()) {
            return new ArrayList<>();
        }
        // 转换为List<Map<String, Object>>
        List<Map<String, Object>> mapList = new ArrayList<>(rawDataList.size());
        for (Object item : rawDataList) {
            // 确保元素是Map类型，否则跳过（或根据实际类型处理）
            if (item instanceof Map<?, ?>) {
                // 强制转换为Map<String, Object>
                @SuppressWarnings("unchecked")
                Map<String, Object> dataMap = (Map<String, Object>) item;
                mapList.add(dataMap);
            } else {
                // 非Map类型的处理（根据实际业务需求调整）
                System.out.println("跳过非Map类型的元素: " + item.getClass());
            }
        }
        // 调用原processResultList方法处理
        return processResultList(mapList);
    }


    /**
     * TODO 工具
     * 处理结果列表：转换数值类型，保留特殊字符串（如时间戳）
     */
    public List<Map<String, Object>> processResultList(List<Map<String, Object>> resultList) {
        List<Map<String, Object>> processedList = new ArrayList<>(resultList.size()); // 预设容量
        for (Map<String, Object> item : resultList) {
            Map<String, Object> processedItem = new HashMap<>(item.size()); // 预设容量
            for (Map.Entry<String, Object> entry : item.entrySet()) {
                String key = entry.getKey();
                Object value = entry.getValue();
                if (value == null) {
                    processedItem.put(key, null);
                    continue;
                }
                String strValue = value.toString().trim();
                // 保留空字符串
                if (strValue.isEmpty()) {
                    processedItem.put(key, strValue);
                    continue;
                }
                // 核心逻辑：特殊字段保持String类型
                if (isPreserveAsString(key, strValue)) {
                    processedItem.put(key, escapeSpecialChars(strValue)); // 安全转义
                }
                // 数值类型转换 - 统一转为BigDecimal避免溢出
                else if (isNumeric(strValue)) {
                    try {
                        processedItem.put(key, new BigDecimal(strValue));
                    } catch (NumberFormatException e) {
                        processedItem.put(key, strValue);
                    }
                }
                // 其他字段保持String并转义
                else {
                    processedItem.put(key, escapeSpecialChars(strValue));
                }
            }
            processedList.add(processedItem);
        }
        return processedList;
    }

    /**
     * TODO 工具
     * 判断是否需要保持为String类型
     * 1. 时间戳字段（如starttime）
     * 2. 类似"20220507025410104"的17位数字字符串（时间格式）
     */
    private boolean isPreserveAsString(String key, String value) {
        // 时间相关字段集合（使用Java 8支持的HashSet初始化）
        Set<String> timeFields = new HashSet<>(Arrays.asList(
                "starttime", "endtime", "gxsj", "inserttime",
                "fqsj", "deletetime", "tcrq"
        ));
        if (timeFields.contains(key)) {
            return true;
        }
        // 格式匹配：17位数字（如20220507025410104）
        if (value.matches("^\\d{17}$")) {
            return true;
        }
        return false;
    }

    /**
     * TODO 工具
     * 判断是否为数字（整数或小数）
     */
    private boolean isNumeric(String value) {
        return value.matches("^[-+]?\\d+$") || value.matches("^[-+]?\\d+\\.\\d+$");
    }

    /**
     * TODO 工具
     * 转义特殊字符，防止XSS攻击
     */
    private String escapeSpecialChars(String value) {
        if (value == null) {
            return null;
        }
        return value.replaceAll("&", "&amp;")
                .replaceAll("<", "&lt;")
                .replaceAll(">", "&gt;")
                .replaceAll("\"", "&quot;")
                .replaceAll("'", "&#39;");
    }


    /**************************************************【下面是管线距离计算】***************************************************/

//    public Map<String, Object> queryPipelineLength(QueryPipelineLengthDTO dto) {
//        // 1. 校验入参非空
//        if (dto == null || dto.getTables() == null || dto.getTables().isEmpty()) {
//            return Collections.singletonMap("error", "入参DTO或表名不能为空");
//        }
//        String tableName = dto.getTables().get(0);
//        String firstPoslon = dto.getFirstPoslon();
//        String firstPoslat = dto.getFirstPoslat();
//        String endPoslon = dto.getEndPoslon();
//        String endPoslat = dto.getEndPoslat();
//        // 校验经纬度参数非空
//        if (StringUtils.isEmpty(firstPoslon) || StringUtils.isEmpty(firstPoslat)
//                || StringUtils.isEmpty(endPoslon) || StringUtils.isEmpty(endPoslat)) {
//            return Collections.singletonMap("error", "起点或终点经纬度不能为空");
//        }
//
//        // 2. 查询起点和终点信息
//        Map<String, Object> objectOneMap = point(firstPoslon, firstPoslat, tableName);
//        if (ObjectUtils.isEmpty(objectOneMap)) {
//            return Collections.singletonMap("error", "未查询到起点关联数据");
//        }
//
//        Map<String, Object> objectTwoMap = point(endPoslon, endPoslat, tableName);
//        // 修复原逻辑错误：此处应判断objectTwoMap而非objectOneMap
//        if (ObjectUtils.isEmpty(objectTwoMap)) {
//            return Collections.singletonMap("error", "未查询到终点关联数据");
//        }
//
//        // 3. 安全获取坐标和关联标识（避免null.toString()异常）
//        String firstX = String.valueOf(objectOneMap.getOrDefault("x", ""));
//        String firstY = String.valueOf(objectOneMap.getOrDefault("y", ""));
//        String firstSource = String.valueOf(objectOneMap.getOrDefault("source", ""));
//        String endX = String.valueOf(objectTwoMap.getOrDefault("x", ""));
//        String endY = String.valueOf(objectTwoMap.getOrDefault("y", ""));
//        String endTarget = String.valueOf(objectTwoMap.getOrDefault("target", ""));
//        // 校验关键标识非空
//        if (StringUtils.isEmpty(firstSource) || StringUtils.isEmpty(endTarget)) {
//            return Collections.singletonMap("error", "起点source或终点target为空");
//        }
//        // 4. 查询GeoJSON数据并校验
//        Map<String, Object> objectMap = jsonList(firstSource, endTarget, tableName);
//        if (ObjectUtils.isEmpty(objectMap) || !objectMap.containsKey("complete_geojson")) {
//            return Collections.singletonMap("error", "未查询到完整的GeoJSON数据");
//        }
//        // 5. 解析坐标并计算管线长度
//        String json = String.valueOf(objectMap.get("complete_geojson"));
//        String coordinateList = extractCoordinates(json); // 假设extractCoordinates已实现非空处理
//        // 校验坐标列表非空
//        if (StringUtils.isEmpty(coordinateList)) {
//            return Collections.singletonMap("error", "提取坐标列表失败");
//        }
//        Map<String, Object> pipeLineLength = pipeLineLength(firstX, firstY, endX, endY, coordinateList);
//        // 6. 处理GeoJSON（添加起点和终点）
//        Map<String, Object> geoJsonData;
//        try {
//            geoJsonData = getGeoJson(objectMap);
//        } catch (NumberFormatException e) {
//            return Collections.singletonMap("error", "经纬度格式错误，无法转换为数字：" + e.getMessage());
//        }
//        // 7. 组装返回结果
//        Map<String, Object> result = new HashMap<>();
//        result.put("pipelength", pipeLineLength.getOrDefault("segment_length_meters", 0));
//        result.put("geoJsonData", geoJsonData);
//        // 修复日志占位符使用错误
//        System.out.printf("处理后的geoJsonData：%s%n", geoJsonData);
//        return result;
//    }
//



    public Map<String, Object> queryPipelineLength(QueryPipelineLengthDTO dto) {
        // 1. 校验入参非空
        if (dto == null || dto.getTables() == null || dto.getTables().isEmpty()) {
            logger.warn("【获取管线长度】入参DTO或表名不能为空");
            return Collections.singletonMap("error", "入参DTO或表名不能为空");
        }
        String tableName = dto.getTables().get(0);
        String firstPoslon = dto.getFirstPoslon();
        String firstPoslat = dto.getFirstPoslat();
        String endPoslon = dto.getEndPoslon();
        String endPoslat = dto.getEndPoslat();

        // 校验经纬度参数非空
        if (StringUtils.isEmpty(firstPoslon) || StringUtils.isEmpty(firstPoslat)
                || StringUtils.isEmpty(endPoslon) || StringUtils.isEmpty(endPoslat)) {
            logger.warn("【获取管线长度】起点或终点经纬度不能为空");
            return Collections.singletonMap("error", "起点或终点经纬度不能为空");
        }

        // 2. 查询起点和终点信息（需包含source和target字段）
        Map<String, Object> objectOneMap = point(firstPoslon, firstPoslat, tableName);
        if (ObjectUtils.isEmpty(objectOneMap) || !objectOneMap.containsKey("source") || !objectOneMap.containsKey("target")) {
            logger.warn("【获取管线长度】未查询到起点关联数据，或缺少source/target字段");
            return Collections.singletonMap("error", "未查询到起点关联数据");
        }

        Map<String, Object> objectTwoMap = point(endPoslon, endPoslat, tableName);
        if (ObjectUtils.isEmpty(objectTwoMap) || !objectTwoMap.containsKey("source") || !objectTwoMap.containsKey("target")) {
            logger.warn("【获取管线长度】未查询到终点关联数据，或缺少source/target字段");
            return Collections.singletonMap("error", "未查询到终点关联数据");
        }

        // 3. 关键修改1：提取两个点的「完整source/target」（共4个核心值），安全避免空指针
        String oneSource = getSafeString(objectOneMap.get("source"));   // 起点source
        String oneTarget = getSafeString(objectOneMap.get("target"));   // 起点target
        String twoSource = getSafeString(objectTwoMap.get("source"));   // 终点source
        String twoTarget = getSafeString(objectTwoMap.get("target"));   // 终点target
        // 起点/终点坐标（用于后续长度计算，保留原有逻辑）
        String firstX = getSafeString(objectOneMap.getOrDefault("x", ""));
        String firstY = getSafeString(objectOneMap.getOrDefault("y", ""));
        String endX = getSafeString(objectTwoMap.getOrDefault("x", ""));
        String endY = getSafeString(objectTwoMap.getOrDefault("y", ""));

        // 校验4个核心值非空（避免无效组合查询）
        if (StringUtils.isEmpty(oneSource) || StringUtils.isEmpty(oneTarget)
                || StringUtils.isEmpty(twoSource) || StringUtils.isEmpty(twoTarget)) {
            logger.error("【获取管线长度】点信息的source/target为空，oneSource:{}, oneTarget:{}, twoSource:{}, twoTarget:{}",
                    oneSource, oneTarget, twoSource, twoTarget);
            return Collections.singletonMap("error", "起点或终点的source/target字段为空");
        }
        // 4. 关键修改2：基于4个核心值生成「排列组合」（覆盖所有可能连通方向）
        List<ComboVO> combinations = Arrays.asList(
                new ComboVO(oneSource, twoSource, "oneSource->twoSource"),  // 第一个点source → 第二个点source
                new ComboVO(oneSource, twoTarget, "oneSource->twoTarget"),  // 第一个点source → 第二个点target
                new ComboVO(oneTarget, twoSource, "oneTarget->twoSource"),  // 第一个点target → 第二个点source
                new ComboVO(oneTarget, twoTarget, "oneTarget->twoTarget")   // 第一个点target → 第二个点target
        );
        // 5. 线程池初始化（线程命名+固定4线程，便于排查）
        ExecutorService executor = Executors.newFixedThreadPool(4, r -> {
            Thread thread = new Thread(r);
            thread.setName("pipeline-query-thread-" + thread.getId());
            return thread;
        });
        List<Future<QueryResultVO>> futures = new ArrayList<>();
        try {
            // 6. 提交所有组合的查询任务（并行执行）
            for (ComboVO combo : combinations) {
                final ComboVO currentCombo = combo;
                futures.add(executor.submit(() -> {
                    Map<String, Object> resultMap = jsonList(currentCombo.getSource(), currentCombo.getTarget(), tableName);
                    int count = calculateResultCount(resultMap);
                    return new QueryResultVO(resultMap, count, currentCombo);
                }));
            }
            // 7. 等待所有任务完成，筛选features数量最多的最佳组合
            QueryResultVO bestResult = null;
            for (Future<QueryResultVO> future : futures) {
                try {
                    // 超时控制：避免单个任务阻塞
                    QueryResultVO currentResult = future.get(QUERY_TIMEOUT_SEC, TimeUnit.SECONDS);
                    if (currentResult == null) {
                        continue;
                    }
                    // 更新最佳结果（仅当当前数量大于历史最大值时）
                    if (bestResult == null || currentResult.getCount() > bestResult.getCount()) {
                        bestResult = currentResult;
                    }

                } catch (TimeoutException e) {
                    logger.error("【获取管线长度】组合查询超时（{}秒），已跳过", QUERY_TIMEOUT_SEC, e);
                    future.cancel(true); // 取消超时任务，释放线程
                } catch (InterruptedException | ExecutionException e) {
                    logger.error("【获取管线长度】组合查询异常，已跳过", e);
                }
            }
            // 8. 校验最佳结果有效性
            if (bestResult == null || bestResult.getResultMap() == null
                    || !bestResult.getResultMap().containsKey("complete_geojson")) {
                logger.warn("【获取管线长度】所有组合均未查询到有效GeoJSON数据");
                return Collections.singletonMap("error", "所有组合均未查询到有效数据");
            }
            // 9. 原有逻辑：解析坐标+计算管线长度
            String json = getSafeString(bestResult.getResultMap().get("complete_geojson"));
            String coordinateList = extractCoordinates(json);
            if (StringUtils.isEmpty(coordinateList)) {
                logger.error("【获取管线长度】从GeoJSON提取坐标列表失败");
                return Collections.singletonMap("error", "提取坐标列表失败");
            }
            Map<String, Object> pipeLineLength = pipeLineLength(firstX, firstY, endX, endY, coordinateList);

            // 10. 原有逻辑：处理GeoJSON格式
            Map<String, Object> geoJsonData;
            try {
                geoJsonData = getGeoJson(bestResult.getResultMap());
            } catch (NumberFormatException e) {
                logger.error("【获取管线长度】经纬度格式错误", e);
                return Collections.singletonMap("error", "经纬度格式错误：" + e.getMessage());
            }

            // 11. 组装返回结果（保留原有结构）
            Map<String, Object> result = new HashMap<>();
            result.put("pipelength", pipeLineLength.getOrDefault("segment_length_meters", 0));
            result.put("geoJsonData", geoJsonData);
            logger.info("【获取管线长度】查询成功，最佳组合[{}]，管线长度:{}米,features数量:{}",
                    bestResult.getCombo().getName(), result.get("pipelength"),bestResult.getCount());
            return result;

        } finally {
            // 强制关闭线程池，避免资源泄漏
            executor.shutdown();
            try {
                if (!executor.awaitTermination(10, TimeUnit.SECONDS)) {
                    executor.shutdownNow(); // 10秒后仍未关闭，强制终止任务
                }
            } catch (InterruptedException e) {
                executor.shutdownNow();
            }
        }
    }


    // 计算结果数量
//    private int calculateResultCount(Map<String, Object> resultMap) {
//        try {
//            String json = String.valueOf(resultMap.get("complete_geojson"));
//            if (json.contains("features")) {
//                // 实际应用中建议使用JSON解析库计算特征数量
//                return json.split("feature").length - 1;
//            }
//        } catch (Exception e) {
//            logger.warn("计算结果数量失败", e);
//        }
//        return 0;
//    }

    /**
     * 基于Jackson解析GeoJSON，精准计算features数量
     * @param resultMap 包含complete_geojson的查询结果
     * @return features数组长度（无features则返回0，解析失败返回0）
     */
    private int calculateResultCount(Map<String, Object> resultMap) {
        // 1. 安全获取complete_geojson字符串
        if (resultMap == null || !resultMap.containsKey("complete_geojson")) {
            logger.warn("calculateResultCount：resultMap为空或无complete_geojson字段");
            return 0;
        }
        Object geoJsonObj = resultMap.get("complete_geojson");
        if (geoJsonObj == null) {
            logger.warn("calculateResultCount：complete_geojson值为null");
            return 0;
        }
        String geoJsonStr = String.valueOf(geoJsonObj);
        if (geoJsonStr.trim().isEmpty()) {
            logger.warn("calculateResultCount：complete_geojson为空字符串");
            return 0;
        }
        try {
            // 2. 解析GeoJSON为JsonNode（Jackson核心对象，支持节点导航）
            JsonNode rootNode = objectMapper.readTree(geoJsonStr);
            JsonNode featuresNode = rootNode.get("features");

            // 4. 校验features是否为数组，返回数组长度
            if (featuresNode != null && featuresNode.isArray()) {
                int featureCount = featuresNode.size();
                return featureCount;
            } else {
                logger.warn("calculateResultCount：complete_geojson中无valid的features数组（可能不是FeatureCollection类型）");
                return 0;
            }
        } catch (Exception e) {
            logger.error("calculateResultCount：解析GeoJSON失败", e);
            return 0;
        }
        }

    // 对geojson的结果进行转义
    public Map<String, Object> getGeoJson(Map<String, Object> objectMapGeoJson) {
        if (objectMapGeoJson == null || !objectMapGeoJson.containsKey("complete_geojson")) {
            return new HashMap<>();
        }
        String json = objectMapGeoJson.get("complete_geojson").toString();
        try {
            return objectMapper.readValue(json, new TypeReference<Map<String, Object>>() {
            });
        } catch (Exception e) {
            e.printStackTrace();
            return new HashMap<>();
        }
    }


    //提取二维坐标的工具
    public String extractCoordinates(String json) {
        // 初始化Jackson的ObjectMapper
        ObjectMapper objectMapper = new ObjectMapper();
        // 解析JSON字符串为JsonNode
        JsonNode rootNode = null;
        try {
            rootNode = objectMapper.readTree(json);
        } catch (JsonProcessingException e) {
            throw new RuntimeException(e);
        }

        // 分步获取节点并增加非空判断
        // 1. 获取features节点
        JsonNode featuresNode = rootNode.get("features");
        if (featuresNode == null || !featuresNode.isArray() || featuresNode.isEmpty()) {
            System.out.println("features节点不存在或为空数组");
            return "";
        }

        // 2. 获取features[0]节点
        JsonNode firstFeatureNode = featuresNode.get(0);
        if (firstFeatureNode == null) {
            System.out.println("features数组第一个元素为空");
            return "";
        }

        // 3. 获取geometry节点（增加非空判断）
        JsonNode geometryNode = firstFeatureNode.get("geometry");
        if (geometryNode == null) {
            System.out.println("geometry节点为空");
            return "";
        }

        // 4. 获取coordinates节点
        JsonNode coordinatesNode = geometryNode.get("coordinates");
        if (coordinatesNode == null || !coordinatesNode.isArray()) {
            System.out.println("coordinates节点不存在或不是数组");
            return "";
        }

        // 提取坐标并格式化
        List<String> coordinateList = new ArrayList<>();
        for (JsonNode coord : coordinatesNode) {
            // 每个坐标是 [经度, 纬度] 数组，增加对坐标数组的有效性判断
            if (coord != null && coord.isArray() && coord.size() >= 2) {
                double lon = coord.get(0).asDouble();  // 经度
                double lat = coord.get(1).asDouble();  // 纬度
                coordinateList.add(lon + " " + lat);
            } else {
                System.out.println("无效的坐标格式：" + coord);
            }
        }
        return String.join(",", coordinateList);
    }


    // 第一步，获取坐标的X,Y
    public Map<String, Object> point(String lon, String lat, String tableName) {
        String sql = "WITH fixed_point AS ("
                + "  SELECT ST_Transform(ST_SetSRID(ST_MakePoint( " + lon + ", " + lat + "), 4326), 4549) AS point_geom"
                + "),"
                + "exploded_lines AS ("
                + "  SELECT "
                + "    gid,"
                + "    target,"
                + "    source,"
                + "    (ST_Dump(geom)).geom AS line_geom,  "
                + "    geom AS original_multi_geom       "
                + "  FROM  " + tableName
                + "  WHERE ST_GeometryType(geom) = 'ST_MultiLineString'"
                + "),"
                + "projection_data AS ("
                + "  SELECT "
                + "    gid,"
                + "    target,"
                + "    source,"
                + "    ST_Distance(fp.point_geom, el.line_geom) AS distancea,"
                + "    el.original_multi_geom AS original_geom,"
                + "    ST_LineInterpolatePoint(el.line_geom, ST_LineLocatePoint(el.line_geom, fp.point_geom)) AS projection_geom"
                + "  FROM exploded_lines el, fixed_point fp"
                + ")"
                + "SELECT "
                + "  gid,"
                + "  target,"
                + "  source,"
                + "  ST_X(st_transform(projection_geom, 4490)) AS x, "
                + "  ST_Y(st_transform(projection_geom, 4490)) AS y"
                + "  FROM projection_data"
                + "  ORDER BY distancea"
                + "  LIMIT 1;";
        log.info("point：{}",sql);
        Map<String, Object> objectMap = pool2DataSourceUtil.NpgNormal_GetListDicNo1(sql);
        return objectMap;
    }

    // 从返回的json中构造结构去取数据，二维数组，经纬度之间是空格，
    public Map<String, Object> jsonList(String source, String target, String tableName) {
        // 美化SQL结构：使用缩进分层，为source和target添加单引号
        String sql =
                "SELECT row_to_json(featcoll) AS complete_geojson " +
                        "FROM ( " +
                        "SELECT " +
                        "'FeatureCollection' AS type, " +
                        "array_to_json(array_agg(row_to_json(feat))) AS features " +
                        "FROM ( " +
                        "SELECT " +
                        "'Feature' AS type, " +
                        "ST_AsGeoJSON(st_transform(final_path.path_geometry, 4490))::text::json AS geometry " +
                        "FROM ( " +
                        "WITH path_edges AS ( " +
                        "SELECT " +
                        "P.seq, " +
                        "e.geom " +
                        "FROM " +
                        "pgr_dijkstra( " +
                        "'SELECT gid AS id, source, target, length AS cost FROM " + tableName + "', " +
                        "'" + source + "', '" + target + "', FALSE, FALSE " +
                        ") P " +
                        "JOIN " + tableName + " e ON P.id2 = e.gid " +
                        "WHERE " +
                        "P.id2 != -1 " +
                        "ORDER BY " +
                        "P.seq " +
                        ") " +
                        "SELECT " +
                        "ST_LineMerge(ST_Union(geom ORDER BY seq)) AS path_geometry " +
                        "FROM " +
                        "path_edges " +
                        ") final_path " +
                        ") AS feat " +
                        ") AS featcoll;";
        log.info("jsonList：{}",sql);
        Map<String, Object> json = pool2DataSourceUtil.NpgNormal_GetListDicNo1(sql);
        return json;
    }


    // 计算距离
    public Map<String, Object> pipeLineLength(String firstX, String firstY, String endX, String endY, String coordinateList) {
        // 移除所有\n，修正ST_SetSRID后的多余逗号，保持缩进结构
        String sql =
                "WITH line_data AS ( " +
                        "SELECT ST_GeomFromText( " +
                        "'LINESTRING(" + coordinateList + ")', 4326 " +
                        ") AS line_geom " +
                        "), points AS ( " +
                        "SELECT " +
                        "ST_SetSRID(ST_MakePoint(" + firstX + ", " + firstY + "), 4326) AS point1, " +  // 修正此处语法
                        "ST_SetSRID(ST_MakePoint(" + endX + ", " + endY + "), 4326) AS point2 " +  // 移除了多余的逗号
                        "), line_info AS ( " +
                        "SELECT " +
                        "ST_LineLocatePoint(ld.line_geom, p.point1) AS loc1, " +
                        "ST_LineLocatePoint(ld.line_geom, p.point2) AS loc2, " +
                        "ld.line_geom " +
                        "FROM line_data ld, points p " +
                        ") " +
                        "SELECT " +
                        "ST_Length( " +
                        "ST_Transform( " +
                        "ST_LineSubstring(line_geom, LEAST(loc1, loc2), GREATEST(loc1, loc2)), " +
                        "3857 " +
                        ") " +
                        ") AS segment_length_meters " +
                        "FROM line_info;";
        log.info("pipeLineLength：{}",sql);
        Map<String, Object> length = pool2DataSourceUtil.NpgNormal_GetListDicNo1(sql);
        return length;
    }




















}
